
import os
import pyodbc
import pandas as pd
import Get_HTMLtables as GH
import numpy as np
from _sqlite3 import connect
import openpyxl
from openpyxl.reader.excel import load_workbook
global tables
import pyodbc
import re
import os
class PDFTableExtractor:
    def __init__(self, ID, find_keyword,Html_file):
        self.ID = ID
        read_html_obj = GH.ReadHtml()  # 创建GH.ReadHtml实例
        self.ZBDW = read_html_obj.read_DW(Html_file, find_keyword)
        read_html_obj = GH.ReadHtml()  # 创建GH.ReadHtml实例
    def get_EPBH(self):
        server = 'SHPUBSRV02'
        database = 'JYCFI'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        query = """
                SELECT DISTINCT EPBH
                FROM [10.106.22.51].JYPRIME.dbo.usrQYMB GG 
                WHERE QYBH= ?
                    """
        IGSDM = str(self.IGSDM)
        self.EPBH = pd.read_sql(query, conn, params=[IGSDM])
        try:
            self.EPBH = self.EPBH['EPBH'][0]
        except:
            self.EPBH = 'EP未匹配成功，需要人工处理'
            return self.EPBH
    def get_GG(self):
        server = 'SHPUBSRV01'
        database = 'JYPRIME'
        conn = pyodbc.connect(
            'Driver={SQL Server};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;')
        ID = self.ID
        query = """
                               SELECT ID,IGSDM,CONVERT(varchar(10),XXFBRQ,23) AS XXFBRQ,CONVERT(varchar(10),JZRQ,23) AS JZRQ,XXBT,GGLJ
                               FROM usrGSGGYWFWB GG 
                               WHERE ID= CAST(? AS NVARCHAR)
                                   """
        self.GG = pd.read_sql(query, conn, params=[ID])
        if not self.GG.empty:
            self.IGSDM = self.GG['IGSDM'][0]
            self.XXFBRQ = self.GG['XXFBRQ'][0]
            self.JZRQ = self.GG['JZRQ'][0]
            self.XXLY = self.GG['XXBT'][0]

            return self.GG
    def find_keyword_and_extract_table(self, Html_file, find_keyword):
        table = GH.ReadHtml.read_html_file(Html_file, find_keyword)
        if table.empty :
            table=GH.ReadHtml.read_html_file(Html_file,find_keyword=['行业分类','占营业成本比重'])
            read_html_obj = GH.ReadHtml()
            self.ZBDW = read_html_obj.read_DW(Html_file, find_keyword=['行业分类','占营业成本比重'])
        if table.shape[0]!=0:
            table = table.drop(0,axis=0)
            table=table.drop(1,axis=0)
            table=table.drop(4,axis=1)
            table=table.drop(5,axis=1)
            new_columns=['SJLMYMC','SJLMSMC','成本','成本占比','成本同比']
            table = table .rename(columns=dict(zip(table.columns, new_columns)))
        return table
    def xggs(self, table):
        new_excel_file_path=f'{self.EPBH}_{self.JZRQ}_成本分析表.xlsx'
        with pd.ExcelWriter(new_excel_file_path, engine='openpyxl') as writer:
            table=pd.melt(table,
                          id_vars=['SJLMYMC','SJLMSMC'],
                          value_vars=list(table.columns)[1:],
                          var_name='ZBMC',
                          value_name='ZBSJ')
            table['XXLY'] = None
            table['XXFBRQ'] = None
            table['JZRQ'] = None
            table['ZTYSMC'] = None
            table['SJLMY'] = None
            table['SJLME'] = None
            table['SJLMS'] = None
            table['TJKJ'] = None
            table['JZRQ'] = None
            table['TJQJ'] = None
            table['JYYWLXDM'] = None
            table['EPBH']=self.EPBH
            table['XXLY'].iloc[:len(table)] = self.XXLY
            table['XXFBRQ'].iloc[:len(table)] = self.XXFBRQ
            table['JZRQ'].iloc[:len(table)] = self.JZRQ
            table['TJQJ'].iloc[:len(table)] = '年'
            table.loc[table['SJLMSMC'].notna() & (table['SJLMSMC'] != '') & (table['SJLMSMC'].notnull()), 'SJLMS'] = '其他'
            DW_conditions=[(table['ZBMC']=='成本'),(table['ZBMC']=='成本占比')
                        ,(table['ZBMC']=='成本同比')]
            table.loc[pd.concat(DW_conditions, axis=1).any(axis=1), 'ZBDW'] = ''
            DW_VALUES=[self.ZBDW,'%','%']
            table['ZBDW'].replace('', np.nan, inplace=True)
            for condition, value in zip(DW_conditions, DW_VALUES):
                table.loc[condition & table['ZBDW'].isna(), 'ZBDW'] = value
            KJ_conditions=[(table['ZBMC']=='成本'),(table['ZBMC']=='成本占比'),
                           (table['ZBMC'] == '成本同比')]
            KJ_VALUES=['当期值','当期值','当期同比增长率']
            table['TJKJ']=np.select(KJ_conditions, KJ_VALUES)
            table['JYYWLXDM'].iloc[:len(table)] = '成本构成'

            table.loc[table['SJLMYMC'].notna() & (table['SJLMYMC'] != ''), 'SJLMY'] = '产品'
            table['ZBMC']=table['ZBMC'].replace('成本同比','成本')
            table['ZBDM'] = table.merge(self.ZB_CODE, on='ZBMC', how='left')['ZBDM']
            table['ZBSJ']=table['ZBSJ'].replace('%','',regex=True)
            table = table.reindex(
                columns=['EPBH', 'XXLY', 'XXFBRQ', 'JZRQ', 'JYYWLXDM', 'SJLMY', 'SJLMYMC', 'SJLMYDM',
                         'SJLME', 'SJLMEMC', 'SJLMEDM', 'SJLMS', 'SJLMSMC', 'ZTYSMC', 'ZBDM', 'ZBMC', 'ZBSJ',
                         'ZBDW', 'TJKJ', 'TJQJ'])
            table.to_excel(writer, index=False, sheet_name='1')
            return table
    def get_code(self):
        db_name = r'C:\Users\yuankf47185\PycharmProjects\客户端尝试\mydatabase.db'
        con = connect(db_name)
        Product = "SELECT * FROM product_code"
        self.PRODUCT_CODE = pd.read_sql(Product, con)
        ZB = 'SELECT * FROM ZB_CODE'
        self.ZB_CODE = pd.read_sql(ZB, con)
        DQ = 'SELECT * FROM city_code'
        self.DQ_CODE = pd.read_sql(DQ, con)
        BT='SELECT * FROM GG  '
        self.BT=pd.read_sql(BT,con)
        con.close()
def run_all(ID,Html_file):
    Html_file=Html_file
    find_keyword = [['产品分类','占营业成本比重'],['成本构成','占营业成本比重']]
    extractor_instance = PDFTableExtractor(ID=ID, find_keyword=find_keyword,Html_file=Html_file)
    extractor_instance.get_GG()
    extractor_instance.get_EPBH()
    table = extractor_instance.find_keyword_and_extract_table(Html_file=Html_file, find_keyword=find_keyword)
    extractor_instance.get_code()
    if table.shape[1] != 0:
        table=extractor_instance.xggs(table)

        return table
    else:
        print('未找到关键词')
    return pd.DataFrame()
if __name__ == '__main__':
    ID = '734474831617'
    Html_file = r'D:\软件打包\Juno-win32.win32.x86_64\Juno-win32.win32.x86_64\734474831617.HTML'
    run_all(ID,Html_file)

